﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Windows.Forms;
using Microsoft.SqlServer.Management.Smo;

namespace QuanLyThuVien.DataAccess
{
    class clsDatabase
    {
        SqlConnection sqlcon;
        SqlCommand sqlcom;
        SqlDataAdapter sqlda;
        SqlDataReader sqldr;
        DataSet ds;
        DataTable dt;
        string strcon = @"Data Source=.\SQLEXPRESS;Initial Catalog=QLTV;Integrated Security=True";

        void MoKeNoi()
        {
            sqlcon = new SqlConnection(strcon);
            if (sqlcon.State == ConnectionState.Closed)
                sqlcon.Open();
        }

        void DongKetNoi()
        {
            sqlcon.Close();
        }

        public void ThuThiSQL(string SQL)
        {
            MoKeNoi();
            sqlcom = new SqlCommand(SQL, sqlcon);
            sqlcom.ExecuteNonQuery();
            DongKetNoi();
        }

        public void LoadLenCombobox(ComboBox cb, string SQL, int chiso)
        {
            cb.Items.Clear();
            MoKeNoi();
            sqlcom = new SqlCommand(SQL, sqlcon);
            sqldr = sqlcom.ExecuteReader();
            while (sqldr.Read())
            {
                cb.Items.Add(sqldr[chiso].ToString());
            }
            DongKetNoi();
        }

        public void LoadLenDG(DataGridView dg, string SQL)
        {
            ds = new DataSet();
            sqlda = new SqlDataAdapter(SQL, strcon);
            sqlda.Fill(ds, "ten bang");
            dg.DataSource = ds.Tables[0];
        }

        public string LoadLenLable(string sql)
        {
            string KetQua = "";
            MoKeNoi();
            sqlcom = new SqlCommand(sql, sqlcon);
            sqldr = sqlcom.ExecuteReader();
            while (sqldr.Read())
            {
                KetQua = sqldr[0].ToString();
            }
            return KetQua;
        }

        public int LayGiaTri(string sql, string dulieuvao)
        {
            MoKeNoi();
            string a = "";
            sqlcom = new SqlCommand(sql, sqlcon);
            sqldr = sqlcom.ExecuteReader();
            while (sqldr.Read())
            {
                if (dulieuvao == sqldr[0].ToString())
                {
                    a = sqldr[1].ToString();
                }
            }
            int n = int.Parse(a);
            return n;
        }

        public bool KiemTra(string sql)
        {
            MoKeNoi();
            sqlcom = new SqlCommand(sql, sqlcon);
            int n = (int)sqlcom.ExecuteScalar();
            DongKetNoi();
            if (n > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        public bool KiemTraUser(string SQL,string User, string Pass)
        {
            MoKeNoi();
            bool a = true;
            sqlcom = new SqlCommand(SQL, sqlcon);
            sqldr = sqlcom.ExecuteReader();
            while (sqldr.Read())
            {
                if (User == sqldr[0].ToString() && Pass == sqldr[1].ToString())
                {
                   a= false;
                }
                else
                {
                    a= true;
                }
            }
            return a;
        }

        public bool KiemTraUserName(string strSQL)
        {
            MoKeNoi();
            //
            sqlcom = new SqlCommand(strSQL, sqlcon);
            int TonTai = (int)(sqlcom.ExecuteScalar());
            //
            DongKetNoi();
            //
            if (TonTai > 0) { return false; }
            else { return true; };
        }

        public void AttachDatabase(string strServerName, string strDatabaseName)
        {
            bool check = false;
            Server sv = new Server(strServerName);
            //Lấy danh sách các CSDL và đưa vào Listbox
            foreach (Database db in sv.Databases)
            {
                if (db.Name == strDatabaseName)
                {
                    check = true;
                }
            }
            //Kiểm tra biến check:
            //Nếu bằng False: Chưa có trong CSDL thì Attach vào và ngược lại
            if (check == false)
            {
                //Trủ tới 2 file .mdf và ldf trong thư mục Debug(Có thể dùng Environment hoặc Application)
                string strPathData = Environment.CurrentDirectory + @"\" + strDatabaseName + ".mdf";
                string strPathLog = Environment.CurrentDirectory + @"\" + strDatabaseName + "_Log.ldf";

                //Chuỗi kết nối
                //string strCon = @"Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Master; Data Source=.\SQLEXPRESS";
                //string strAttach = "EXEC sp_attach_db 'SampleDBProgramming', '" + strPathData + "','" + strPathLog + "' ";
                string strCon = @"Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Master; Data Source='" + strServerName + "' ";
                string strAttach = "EXEC sp_attach_db '" + strDatabaseName + "', '" + strPathData + "','" + strPathLog + "' ";

                //Thực thi như một câu lệnh SQL
                sqlcon = new SqlConnection(strCon);
                sqlcon.Open();
                sqlcom = new SqlCommand(strAttach, sqlcon);
                sqlcom.ExecuteNonQuery();
                sqlcon.Close();
            }
            else
            {
                MessageBox.Show("Cơ sở dữ liệu: " + strDatabaseName + " đã tồn tại", "Attach");
            }
        }
    }
}
